package com.eascs.web.o2o.web.service;

import com.alibaba.dubbo.config.annotation.Reference;
import com.eascs.common.util.StopWatch;
import com.eascs.web.o2o.web.service.impl.BaseService;
import com.eascs.workflow.common.entity.sys.ActOrgEntity;
import com.eascs.workflow.common.service.sys.SysOrgService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.inject.Inject;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Service
public class BuildDataService {
	
	protected final Logger logger =LoggerFactory.getLogger(getClass());
	@Inject
	BaseService baseService;
	@Reference
	SysOrgService sysOrgService;
	
	
	
	
	
	
	
	
	

	public List<Object[]> getBankData(Date month) {
		logger.info("开始同步银行明细报表");
		//删除之前的数据
		baseService.isnertNewBusinessfromods("delete from ods_o2odev_bank_detail");
		//银行,审批额度
		String sql1="sql.BuildExcelService.sql1";
		int count=0;
		try {
			List<Object[]> data1 = baseService.findObjectsListfromods(sql1);
			for (int i = 0; i < data1.size(); i++) {//更新报表每一列的数据
				count++;
				Object[] b=data1.get(i);
				double approvalAmount =0;
				if(b!=null){
					String bank=(String) b[0];
					approvalAmount = decideBankQuota(bank);
					baseService.isnertNewBusinessfromods("insert into ods_o2odev_bank_detail(id,refund_bank,approval_amount) values(?,?,?)",count, bank,approvalAmount);
				}
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：放款银行,审批额度=========================================");
			throw e;
		}
		
		//累计投放总量（累计笔数，金额）
		String sql2="sql.BuildExcelService.sql2";
		try {
			List<Object[]> data2 = baseService.findObjectsListfromods(sql2);
			for (int i = 0; i < data2.size(); i++) {//更新报表每一列的数据
				Object[] b=data2.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_bank_detail set loan_num = ?,loan_amount=? where refund_bank = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：累计投放总量（累计笔数，金额）统计异常=========================================");
			throw e;
		}
		//累计投放总量(余额)
		String sql3="sql.BuildExcelService.sql3";
		try {
			List<Object[]> data3 = baseService.findObjectsListfromods(sql3);
			for (int i = 0; i < data3.size(); i++) {//更新报表每一列的数据
				Object[] b=data3.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_bank_detail set loan_balance = ? where refund_bank = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：累计投放总量(余额)统计异常=========================================");
			throw e;
		}
		
		//当月投放(累计笔数，金额)
		String sql4="sql.BuildExcelService.sql4";
		try {
			List<Object[]> data4 = baseService.findObjectsListfromods(sql4,month,month);
			for (int i = 0; i < data4.size(); i++) {//更新报表每一列的数据
				Object[] b=data4.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_bank_detail set month_loan_num = ? ,month_loan_amount=?  where refund_bank = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：当月投放(累计笔数，金额)统计异常=========================================");
			throw e;
		}
		
		//剩余额度
		String sql5="sql.BuildExcelService.sql5";
		try {
			List<Object[]> data5 = baseService.findObjectsListfromods(sql5);
			for (int i = 0; i < data5.size(); i++) {//更新报表每一列的数据
				Object[] b=data5.get(i);
				if(b!=null){
					BigDecimal residualAmount=(BigDecimal) b[1];
					double sum=0;
					if(residualAmount==null||residualAmount.doubleValue()<0){
						sum=0.0;
					}else{
						sum=residualAmount.doubleValue();
					}
					baseService.isnertNewBusinessfromods("update ods_o2odev_bank_detail set residual_amount = ? where refund_bank = ? ",sum,  b[0]);
				}
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：剩余额度 统计异常=========================================");
			throw e;
		}
		
	
		String sql6="sql.BuildExcelService.sql6";
		List<Object[]> datas =baseService.findObjectsListfromods(sql6);
		logger.info("结束同步银行明细报表");
		return datas;
	}
	
	
	
	
	
	
	
	public List<Object[]> getPlatData(Date month) {
		logger.info("开始同步月报-平台明细报表");
		//删除之前的数据
		baseService.isnertNewBusinessfromods("delete from ods_o2odev_plat_detail");
		
		//月报-平台明细
		String sql1="sql.BuildExcelService.sql7";
		logger.info("插入月报-平台明细的平台以及省区等字段开始......");
		try {
			baseService.isnertNewBusinessfromods(sql1,month);
		} catch (Exception e) {
			logger.error("插入月报-平台明细的平台以及省区等字段出现异常......");
			throw e;
		}
		logger.info("插入月报-平台明细的平台以及省区等字段结束......");
		
		//本年新客户投放(笔数,金额)
		String sql2="sql.BuildExcelService.sql8";
		try {
			List<Object[]> data2 = baseService.findObjectsListfromods(sql2,month);
			for (int i = 0; i < data2.size(); i++) {//更新报表每一列的数据
				Object[] b=data2.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_plat_detail set loan_num_new_cus_year = ?,loan_amount_new_cus_year=? where ltd_name = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本年新客户投放(笔数,金额)统计异常=========================================");
			throw e;
		}
		
		//新客户投放量占比%
		String sql3="sql.BuildExcelService.sql9";
		try {
				baseService.isnertNewBusinessfromods(sql3);
		} catch (Exception e) {
			logger.info("BuildExcelService：新客户投放量占比%统计异常=========================================");
			throw e;
		}
		
		//月度累计投放量(累计笔数，金额)
		String sql4="sql.BuildExcelService.sql10";
		try {
			List<Object[]> data4 = baseService.findObjectsListfromods(sql4,month,month);
			for (int i = 0; i < data4.size(); i++) {//更新报表每一列的数据
				Object[] b=data4.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_plat_detail set loan_num_month = ? ,loan_amount_month=?  where ltd_name = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：月度累计投放量(累计笔数，金额)统计异常=========================================");
			throw e;
		}
		
		//月度累计投放量(本月新客户投放)
		String sql5="sql.BuildExcelService.sql11";
		try {
			List<Object[]> data5 = baseService.findObjectsListfromods(sql5,month,month);
			for (int i = 0; i < data5.size(); i++) {//更新报表每一列的数据
				Object[] b=data5.get(i);
					baseService.isnertNewBusinessfromods("update ods_o2odev_plat_detail set loan_num_new_cus_month = ? ,loan_amount_new_cus_month=?  where ltd_name = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：月度累计投放量(本月新客户投放) 统计异常=========================================");
			throw e;
		}
		
		
		//新客户投放量占比%
		String sql6="sql.BuildExcelService.sql12";
		try {
				baseService.isnertNewBusinessfromods(sql6);
		} catch (Exception e) {
			logger.info("BuildExcelService：新客户投放量占比%统计异常=========================================");
			throw e;
		}
	
		String sql7="sql.BuildExcelService.sql13";
		List<Object[]> datas =baseService.findObjectsListfromods(sql7);
		logger.info("结束同步月报-平台明细报表");
		return datas;
	}
	
	
	
	public List<Object[]> getProvinceData(Date month) {
		logger.info("开始同步月报-省区明细报表");
		//删除之前的数据
		baseService.isnertNewBusinessfromods("delete from ods_o2odev_province_detail");
		
		//月报-省区明细
		String sql1="sql.BuildExcelService.sql14";
		logger.info("插入月报-省区明细字段开始......");
		try {
			baseService.isnertNewBusinessfromods(sql1,month,month);
		} catch (Exception e) {
			logger.error("插入月报-省区明细的省区字段出现异常......");
			throw e;
		}
		logger.info("插入月报-省区明细的省区字段结束......");
		
		//新增笔数(笔数,金额)
		String sql2="sql.BuildExcelService.sql15";
		try {
			List<Object[]> data2 = baseService.findObjectsListfromods(sql2,month,month);
			for (int i = 0; i < data2.size(); i++) {//更新报表每一列的数据
				Object[] b=data2.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_province_detail set add_num = ?,add_sum=? where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：新增笔数(笔数,金额)统计异常=========================================");
			throw e;
		}
		
		//续贷笔数(笔数,金额)
		String sql3="sql.BuildExcelService.sql16";
		try {
			List<Object[]> data3 = baseService.findObjectsListfromods(sql3,month,month);
			for (int i = 0; i < data3.size(); i++) {//更新报表每一列的数据
				Object[] b=data3.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_province_detail set extend_num = ?,extend_sum=? where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：续贷笔数(笔数,金额)统计异常=========================================");
			throw e;
		}
			
		//下辖平台总量
		String sql4="sql.BuildExcelService.sql17";
		try {
			List<Object[]> data4 = baseService.findObjectsListfromods(sql4,month);
			for (int i = 0; i < data4.size(); i++) {//更新报表每一列的数据
				Object[] b=data4.get(i);
				if(b!=null){
					String province=(String) b[0];
					List<ActOrgEntity> list=sysOrgService.getChildOrgByName(province);
					if(list!=null&&list.size()>0){
						baseService.isnertNewBusinessfromods("update ods_o2odev_province_detail set under_ltd = ? where province = ? ", list.size()-1,province);
					}
				}
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本年激活平台数量统计异常=========================================");
			throw e;
		}
		
		
		//本年激活平台数量
		String sql5="sql.BuildExcelService.sql18";
		try {
			List<Object[]> data5 = baseService.findObjectsListfromods(sql5,month);
			for (int i = 0; i < data5.size(); i++) {//更新报表每一列的数据
				Object[] b=data5.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_province_detail set active_ltd_year = ? where province = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本年激活平台数量统计异常=========================================");
			throw e;
		}
		
		//本月激活平台数量
		String sql6="sql.BuildExcelService.sql19";
		try {
			List<Object[]> data6 = baseService.findObjectsListfromods(sql6,month,month);
			for (int i = 0; i < data6.size(); i++) {//更新报表每一列的数据
				Object[] b=data6.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_province_detail set active_ltd_month = ? where province = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本月激活平台数量统计异常=========================================");
			throw e;
		}
		
		
		//笔数合计(笔数,金额)
		String sql7="sql.BuildExcelService.sql20";
		try {
			List<Object[]> data7 = baseService.findObjectsListfromods(sql7,month,month);
			for (int i = 0; i < data7.size(); i++) {//更新报表每一列的数据
				Object[] b=data7.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_province_detail set total_num = ?,total_sum=? where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：笔数合计(笔数,金额)统计异常=========================================");
			throw e;
		}
		
		
		//修改大区，等于省区
		baseService.isnertNewBusinessfromods("update ods_o2odev_province_detail set region= province ");
					
		
		//加权投放全国排名
		String sql8="sql.BuildExcelService.sql21";
		try {
			List<Object[]> data8 = baseService.findObjectsListfromods(sql8);
			for (int i = 0; i < data8.size(); i++) {//更新报表每一列的数据
				Object[] b=data8.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_province_detail set weighted_rank = ?   where region = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：加权投放全国排名统计异常=========================================");
			throw e;
		}
		
		//放款总量排名
		String sql9="sql.BuildExcelService.sql22";
		try {
			List<Object[]> data9 = baseService.findObjectsListfromods(sql9);
			for (int i = 0; i < data9.size(); i++) {//更新报表每一列的数据
				Object[] b=data9.get(i);
					baseService.isnertNewBusinessfromods("update ods_o2odev_province_detail set total_loan_rank = ? where region = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：放款总量排名统计异常=========================================");
			throw e;
		}
		
		
	
		String sql10="sql.BuildExcelService.sql23";
		List<Object[]> datas =baseService.findObjectsListfromods(sql10);
		logger.info("结束同步月报-省区明细报表");
		return datas;
	}


	/*
	 * 日报明细
	 */
	
	public List<Object[]> getDayBankData(Date day) {
		logger.info("开始同步日报银行明细报表");
		//删除之前的数据
		baseService.isnertNewBusinessfromods("delete from ods_o2odev_bank_day_detail");
		//银行,审批额度
		String sql1="sql.dayReport.sql11";
		int count=0;
		try {
			List<Object[]> data1 = baseService.findObjectsListfromods(sql1);
			for (int i = 0; i < data1.size(); i++) {//更新报表每一列的数据
				count++;
				Object[] b=data1.get(i);
				double approvalAmount =0;
				if(b!=null){
					String bank=(String) b[0];
					approvalAmount = decideBankQuota(bank);
					baseService.isnertNewBusinessfromods("insert into ods_o2odev_bank_day_detail(id,refund_bank,approval_amount) values(?,?,?)",count, bank,approvalAmount);
				}
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：放款银行,审批额度=========================================");
			throw e;
		}
		
		//累计投放总量（累计笔数，金额）
		String sql2="sql.dayReport.sql12";
		try {
			List<Object[]> data2 = baseService.findObjectsListfromods(sql2);
			for (int i = 0; i < data2.size(); i++) {//更新报表每一列的数据
				Object[] b=data2.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_bank_day_detail set loan_num = ?,loan_amount=? where refund_bank = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：累计投放总量（累计笔数，金额）统计异常=========================================");
			throw e;
		}
		//累计投放总量(余额)
		String sql3="sql.dayReport.sql13";
		try {
			List<Object[]> data3 = baseService.findObjectsListfromods(sql3);
			for (int i = 0; i < data3.size(); i++) {//更新报表每一列的数据
				Object[] b=data3.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_bank_day_detail set balance_amount = ? where refund_bank = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：累计投放总量(余额)统计异常=========================================");
			throw e;
		}
		
		//当月投放(累计笔数，金额)
		String sql4="sql.dayReport.sql14";
		try {
			List<Object[]> data4 = baseService.findObjectsListfromods(sql4,day,day);
			for (int i = 0; i < data4.size(); i++) {//更新报表每一列的数据
				Object[] b=data4.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_bank_day_detail set month_num = ? ,month_amount=?  where refund_bank = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：当月投放(累计笔数，金额)统计异常=========================================");
			throw e;
		}
		
		
		//当日投放(累计笔数，金额)
		String sql5="sql.dayReport.sql15";
		try {
			List<Object[]> data5 = baseService.findObjectsListfromods(sql5,day,day,day);
			for (int i = 0; i < data5.size(); i++) {//更新报表每一列的数据
				Object[] b=data5.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_bank_day_detail set day_num = ? ,day_amount=?  where refund_bank = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：当日投放(累计笔数，金额)统计异常=========================================");
			throw e;
		}
		//剩余额度
		String sql6="sql.dayReport.sql16";
		try {
			List<Object[]> data6 = baseService.findObjectsListfromods(sql6);
			for (int i = 0; i < data6.size(); i++) {//更新报表每一列的数据
				Object[] b=data6.get(i);
				if(b!=null){
					BigDecimal residualAmount=(BigDecimal) b[1];
					double sum=0;
					if(residualAmount==null||residualAmount.doubleValue()<0){
						sum=0.0;
					}else{
						sum=residualAmount.doubleValue();
					}
					baseService.isnertNewBusinessfromods("update ods_o2odev_bank_day_detail set residual_amount = ? where refund_bank = ? ",sum,  b[0]);
				}
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：剩余额度 统计异常=========================================");
			throw e;
		}
		
	
		String sql7="sql.dayReport.sql17";
		List<Object[]> datas =baseService.findObjectsListfromods(sql7);
		logger.info("结束同步银行明细报表");
		return datas;
	}
	
	
	
	public List<Object[]> getDayProvinceData(Date day) {
		logger.info("开始同步日报-省区明细报表");
		//删除之前的数据
		baseService.isnertNewBusinessfromods("delete from ods_o2odev_province_day_detail");
		
		//日报-省区明细
		String sql1="sql.dayReport.sql1";
		logger.info("插入日报-省区明细字段开始......");
		try {
			baseService.isnertNewBusinessfromods(sql1,day,day);
		} catch (Exception e) {
			logger.error("插入日报-省区明细的省区字段出现异常......");
			throw e;
		}
		logger.info("插入日报-省区明细的省区字段结束......");
		
		//本月累计放款(笔数,金额)
		String sql2="sql.dayReport.sql2";
		try {
			List<Object[]> data2 = baseService.findObjectsListfromods(sql2,day,day);
			for (int i = 0; i < data2.size(); i++) {//更新报表每一列的数据
				Object[] b=data2.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_province_day_detail set loan_sum = ?,loan_amount=? where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本月累计放款(笔数,金额)统计异常=========================================");
			throw e;
		}
		
		//上月累计放款(金额)
		String sql3="sql.dayReport.sql3";
		try {
			List<Object[]> data3 = baseService.findObjectsListfromods(sql3,day,day);
			for (int i = 0; i < data3.size(); i++) {//更新报表每一列的数据
				Object[] b=data3.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_province_day_detail set last_loan_amount = ?  where province = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：上月累计放款(金额)统计异常=========================================");
			throw e;
		}
			
		//本月累计放款续贷（笔数，金额）
		String sql4="sql.dayReport.sql4";
		try {
			List<Object[]> data4 = baseService.findObjectsListfromods(sql4,day,day);
			for (int i = 0; i < data4.size(); i++) {//更新报表每一列的数据
				Object[] b=data4.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_province_day_detail set extend_sum = ?,extend_amount=?  where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本月累计放款续贷（笔数，金额）统计异常=========================================");
			throw e;
		}
		
		
		//本月累计放款新增（笔数，金额）
		String sql5="sql.dayReport.sql5";
		try {
			List<Object[]> data5 = baseService.findObjectsListfromods(sql5,day,day);
			for (int i = 0; i < data5.size(); i++) {//更新报表每一列的数据
				Object[] b=data5.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_province_day_detail set add_sum = ?,add_amount=? where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：本月累计放款新增（笔数，金额）=========================================");
			throw e;
		}
		
		//当日放款（笔数，金额）
		String sql6="sql.dayReport.sql6";
		try {
			List<Object[]> data6 = baseService.findObjectsListfromods(sql6,day,day,day);
			for (int i = 0; i < data6.size(); i++) {//更新报表每一列的数据
				Object[] b=data6.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_province_day_detail set day_loan_sum = ?,day_loan_amount=? where province = ? ", b[1],b[2],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：当日放款（笔数，金额）统计异常=========================================");
			throw e;
		}
		
		baseService.isnertNewBusinessfromods("update ods_o2odev_province_day_detail set add_rage= add_amount/loan_amount");
		
		//广东、海南，合并为广东大区，四川、重庆，合并为川渝大区
		baseService.isnertNewBusinessfromods("update ods_o2odev_province_day_detail set region= (case when province='广东' then '广东大区' when province='海南' then '广东大区' when province='四川' then '川渝大区' when province='重庆' then '川渝大区' else province  end ) ");
			
		
		
		//金额排名
		String sql7="sql.dayReport.sql7";
		try {
			List<Object[]> data7 = baseService.findObjectsListfromods(sql7);
			for (int i = 0; i < data7.size(); i++) {//更新报表每一列的数据
				Object[] b=data7.get(i);
				baseService.isnertNewBusinessfromods("update ods_o2odev_province_day_detail set amount_rank = ?   where region = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：加权投放全国排名统计异常=========================================");
			throw e;
		}
		
		//加权排名
		String sql8="sql.dayReport.sql8";
		try {
			List<Object[]> data8 = baseService.findObjectsListfromods(sql8);
			for (int i = 0; i < data8.size(); i++) {//更新报表每一列的数据
				Object[] b=data8.get(i);
					baseService.isnertNewBusinessfromods("update ods_o2odev_province_day_detail set weighted_rank = ? where region = ? ", b[1],b[0]);
			}
		} catch (Exception e) {
			logger.info("BuildExcelService：放款总量排名统计异常=========================================");
			throw e;
		}
		
		
	
		String sql9="sql.dayReport.sql9";
		List<Object[]> datas =baseService.findObjectsListfromods(sql9);
		logger.info("结束同步日报-省区明细报表");
		return datas;
	}

	/*
	 * 获取奖励发放表基础数据
	 */
	
	public List<Object[]> getRewardData(Date month) {
		logger.info("开始同步O2O金融百亿大战竞赛奖励数据");
		StopWatch stopWatch = new StopWatch();
		//查询奖励数据
		String sql1=" select t2.*,t1.group_name from (SELECT hu.group_name, hu.regional_name province, sum(CASE WHEN l.loan_num > 1 THEN b.loan_amount * 0.4 ELSE b.loan_amount * 0.6 END ) weight_amount FROM ods_o2odev_basic_information_after_loan b INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id WHERE l.loan_type = 'BK' and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?) GROUP BY hu.group_name,hu.regional_name order by hu.group_name,weight_amount desc) t1 inner join (SELECT hu.regional_name province,concat(hu.platform_code,'-',hu.platform_name) ltd,count(b.loan_amount) loan_num,sum(b.loan_amount)/300000 convert_loan_num,    sum(b.loan_amount) loan_amount, sum(case when l.loan_num>1 then  b.loan_amount*0.4 else  b.loan_amount*0.6 end )  weight_amount,  case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else      sum(b.loan_amount)*0.0007 end  plat_award,null  province_award,case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end  plat_sale_award,case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end plat_finace_award,	   null province_finace_award,    0.0 month_top_award,null hq_award,null hq_sale_award, ( case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else sum(b.loan_amount)*0.0007 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end)   total,null month_award FROM ods_o2odev_basic_information_after_loan b 	INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id 	WHERE l.loan_type = 'BK'  and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?)  	GROUP BY hu.group_name,hu.regional_name,l.operator_agency ORDER BY hu.group_name,weight_amount ) t2 on t1.province=t2.province  order by t1.group_name,t1.weight_amount desc,t2.weight_amount desc  ";
		List<Object[]> datas=baseService.findObjectsListfromods(sql1,month,month,month,month);
		//处理“月度业绩标杆”
		if(datas!=null&&datas.size()>0){
			for (int i = 0; i < datas.size(); i++) {
				
				BigDecimal flag=new BigDecimal(10000000);//标准1000万
				BigDecimal flag1=new BigDecimal(0);//此时的最大值
				int  fina=-1;//获奖励的行数
				
				
			    //如果该行为最后一行，进行该的颁奖
			    if(i==datas.size()-1){//最后一行特殊处理
			    	if(fina>0){//该有获奖的
						Object[] o=datas.get(fina);
						o[11]=5000;
						flag1=new BigDecimal(0);
						fina=-1;
					}
			    }else{
			    	Object[] o_pre = datas.get(i);
					Object[] o_next = datas.get(i+1);
					String regionalName_pre =(String) o_pre[0];//当前行所在
					String regionalName_next =(String) o_next[0];//下一行所在
					//获取该行是否获奖的标示
					BigDecimal amount_pre=(BigDecimal) o_pre[4];//放款金额
				    if(amount_pre.compareTo(flag)>0&&amount_pre.compareTo(flag1)>0){
				    	flag1=amount_pre;
				    	fina=i;
				    }
			    	if(!regionalName_pre.equals(regionalName_next)){
						if(fina>0){//该有获奖的
							Object[] o=datas.get(fina);
							o[11]=5000;
							flag1=new BigDecimal(0);
							fina=-1;
						}
					}
			    }
				
			}
		}
	
		logger.info("结束同步O2O金融百亿大战竞赛奖励数据,共耗时[{}]秒",stopWatch.elapsedTime()/1000);
		return datas;
		
	}
	/*
	 * 获取奖励发放表各个省区金融团队合计数据
	 */
	public List<Object[]> getRewardGegData(Date month,String province) {
		logger.info("开始同步O2O金融百亿大战竞赛奖励数据");
		StopWatch stopWatch = new StopWatch();
		//查询奖励数据
		String sql1=" select t.regional_name,'省区金融团队',null,null,null,null,null,case when t.regional_name ='贵州' then sum(t.loan_amount)*0.00033 else sum(t.loan_amount)*0.00035  end  m,null,null,case when t.regional_name ='福建' then   sum(t.loan_num)*200 else sum(t.loan_amount)/1500  end n ,month_top_award,null,null,(CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END),null from  (SELECT hu.regional_name regional_name,concat(hu.platform_code,'-',hu.platform_name) ltd,count(b.loan_amount) loan_num,sum(b.loan_amount)/300000 convert_loan_num,    sum(b.loan_amount) loan_amount, sum(case when l.loan_num>1 then  b.loan_amount*0.4 else  b.loan_amount*0.6 end )  weight_amount,sum(b.loan_amount)*0.0007 plat_award,0 province_award,    case when  sum(b.loan_amount)>300000 then (sum(b.loan_amount)/300000)*700 else 0 end plat_sale_award,    case when  sum(b.loan_amount)>300000 then (sum(b.loan_amount)/300000)*250 else 0 end plat_finace_award,   0 province_finace_award,   0.0 month_top_award,0 hq_award,0 hq_sale_award,    0 total,0.0 month_award FROM ods_o2odev_basic_information_after_loan b 	INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id 	WHERE l.loan_type = 'BK'  and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?) and  hu.regional_name=?	GROUP BY hu.group_name,hu.regional_name,l.operator_agency ORDER BY hu.group_name,weight_amount ) t  ";
		List<Object[]> datas=baseService.findObjectsListfromods(sql1,month,month,province);
		logger.info("结束同步O2O金融百亿大战竞赛奖励数据,共耗时[{}]秒",stopWatch.elapsedTime()/1000);
		return datas;
		
	}
	
	/*
	 * 获取奖励发放表各个合计数据
	 */
	public List<Object[]> getRewardProData(Date month,String province) {
		logger.info("开始同步O2O金融百亿大战竞赛奖励数据");
		StopWatch stopWatch = new StopWatch();
		//查询奖励数据
		String sql1=" select t.group_name,t.regional_name,sum(t.loan_num),sum(t.convert_loan_num),sum(t.loan_amount),sum(t.weight_amount),sum(t.plat_award),case when t.regional_name ='贵州' then sum(t.loan_amount)*0.00033 else sum(t.loan_amount)*0.00035  end,sum(t.plat_sale_award),sum(t.plat_finace_award),case when t.regional_name ='福建' then   sum(t.loan_num)*200 else sum(t.loan_amount)/1500  end,sum(t.month_top_award),sum(t.loan_amount)*0.0002,sum(t.convert_loan_num)*40,sum(t.total)+(CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END)  ,sum(t.month_top_award)+(sum(t.total) + (CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END))*0.6  from  (SELECT hu.group_name,hu.regional_name regional_name,concat(hu.platform_code,'-',hu.platform_name) ltd,count(b.loan_amount) loan_num,sum(b.loan_amount)/300000 convert_loan_num,    sum(b.loan_amount) loan_amount, sum(case when l.loan_num>1 then  b.loan_amount*0.4 else  b.loan_amount*0.6 end )  weight_amount, case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else      sum(b.loan_amount)*0.0007 end  plat_award,null  province_award,    case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end  plat_sale_award,case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end plat_finace_award,	   0 province_finace_award,    0.0 month_top_award,0 hq_award,0 hq_sale_award, ( case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else sum(b.loan_amount)*0.0007 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end)   total,0.0 month_award FROM ods_o2odev_basic_information_after_loan b 	INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id 	WHERE l.loan_type = 'BK'  and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?) and hu.regional_name = ?  	GROUP BY hu.group_name,hu.regional_name,l.operator_agency ORDER BY hu.group_name,weight_amount ) t group by  t.regional_name order by t.group_name ";
		List<Object[]> datas=baseService.findObjectsListfromods(sql1,month,month,province);
		logger.info("结束同步O2O金融百亿大战竞赛奖励数据,共耗时[{}]秒",stopWatch.elapsedTime()/1000);
		return datas;
		
	}
	 
	/*
	 * 获取奖励发放表各个组别合计数据
	 */
	public List<Object[]> getRewardGroData(Date month,String group) {
		logger.info("开始同步O2O金融百亿大战竞赛奖励数据");
		StopWatch stopWatch = new StopWatch();
		List<Object[]> datas=new ArrayList<Object[]>();
		if(group!=null&&group.equals("X")){
			String sql1=" SELECT null,'A组+B组',sum(t1.loan_num),sum(t1.convert_loan_num),sum(t1.loan_amount),sum(t1.weight_amount),sum(t1.plat_award),sum(t1.province_plat_award),sum(t1.plat_sale_award),sum(t1.plat_finace_award),sum(t1.pro_plat_finace_award),sum(t1.month_top_award),sum(t1.gro_award),sum(t1.per_award),sum(t1.total),sum(t1.month_total)  from   (select t.group_name group_name,t.regional_name regional_name ,sum(t.loan_num) loan_num,sum(t.convert_loan_num) convert_loan_num,sum(t.loan_amount) loan_amount,sum(t.weight_amount) weight_amount,sum(t.plat_award) plat_award,case when t.regional_name ='贵州' then sum(t.loan_amount)*0.00033 else sum(t.loan_amount)*0.00035  end province_plat_award,sum(t.plat_sale_award) plat_sale_award,sum(t.plat_finace_award) plat_finace_award,case when t.regional_name ='福建' then   sum(t.loan_num)*200 else sum(t.loan_amount)/1500  end pro_plat_finace_award,sum(t.month_top_award) month_top_award,sum(t.loan_amount)*0.0002 gro_award,sum(t.convert_loan_num)*40 per_award,sum(t.total)+(CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END) total ,sum(t.month_top_award)+(sum(t.total) + (CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END))*0.6  month_total from  (SELECT hu.group_name, hu.regional_name regional_name,concat(hu.platform_code,'-',hu.platform_name) ltd,count(b.loan_amount) loan_num,sum(b.loan_amount)/300000 convert_loan_num,    sum(b.loan_amount) loan_amount, sum(case when l.loan_num>1 then  b.loan_amount*0.4 else  b.loan_amount*0.6 end )  weight_amount, case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else      sum(b.loan_amount)*0.0007 end  plat_award,null  province_award,    case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end  plat_sale_award,case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end plat_finace_award,	   0 province_finace_award,    0 month_top_award,0 hq_award,0 hq_sale_award, ( case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else sum(b.loan_amount)*0.0007 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end)  total,0.0 month_award FROM ods_o2odev_basic_information_after_loan b 	INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id 	WHERE l.loan_type = 'BK'  and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?)  and hu.group_name in ('A','B') 	GROUP BY hu.group_name,hu.regional_name,l.operator_agency ORDER BY hu.group_name,weight_amount ) t group by  regional_name) t1 ";
			datas=baseService.findObjectsListfromods(sql1,month,month);
		}else{
			
			String sql1=" SELECT null,concat(case when t1.group_name='O' then '未分' else t1.group_name end,'组'),sum(t1.loan_num),sum(t1.convert_loan_num),sum(t1.loan_amount),sum(t1.weight_amount),sum(t1.plat_award),sum(t1.province_plat_award),sum(t1.plat_sale_award),sum(t1.plat_finace_award),sum(t1.pro_plat_finace_award),sum(t1.month_top_award),sum(t1.gro_award),sum(t1.per_award),sum(t1.total),sum(t1.month_total)  from   (select t.group_name group_name,t.regional_name regional_name ,sum(t.loan_num) loan_num,sum(t.convert_loan_num) convert_loan_num,sum(t.loan_amount) loan_amount,sum(t.weight_amount) weight_amount,sum(t.plat_award) plat_award,case when t.regional_name ='贵州' then sum(t.loan_amount)*0.00033 else sum(t.loan_amount)*0.00035  end province_plat_award,sum(t.plat_sale_award) plat_sale_award,sum(t.plat_finace_award) plat_finace_award,case when t.regional_name ='福建' then   sum(t.loan_num)*200 else sum(t.loan_amount)/1500  end pro_plat_finace_award,sum(t.month_top_award) month_top_award,sum(t.loan_amount)*0.0002 gro_award,sum(t.convert_loan_num)*40 per_award,sum(t.total)+(CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END) total ,sum(t.month_top_award)+(sum(t.total) + (CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END))*0.6  month_total from  (SELECT hu.group_name, hu.regional_name regional_name,concat(hu.platform_code,'-',hu.platform_name) ltd,count(b.loan_amount) loan_num,sum(b.loan_amount)/300000 convert_loan_num,    sum(b.loan_amount) loan_amount, sum(case when l.loan_num>1 then  b.loan_amount*0.4 else  b.loan_amount*0.6 end )  weight_amount, case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else      sum(b.loan_amount)*0.0007 end  plat_award,null  province_award,    case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end  plat_sale_award,case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end plat_finace_award,	   0 province_finace_award,    0.0 month_top_award,0 hq_award,0 hq_sale_award,( case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else sum(b.loan_amount)*0.0007 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end)   total,0.0 month_award FROM ods_o2odev_basic_information_after_loan b 	INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id 	WHERE l.loan_type = 'BK'  and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?)  and  hu.group_name=? 	GROUP BY hu.group_name,hu.regional_name,l.operator_agency ORDER BY hu.group_name,weight_amount ) t group by  regional_name) t1  ";
			datas=baseService.findObjectsListfromods(sql1,month,month,group);
		}
		logger.info("结束同步O2O金融百亿大战竞赛奖励数据,共耗时[{}]秒",stopWatch.elapsedTime()/1000);
		return datas;
		
	}
	
	
	/*
	 * 获取奖励发放表大合计数据
	 */
	public List<Object[]> getRewardRTotalData(Date month,String flag) {
		logger.info("开始同步O2O金融百亿大战竞赛奖励数据");
		StopWatch stopWatch = new StopWatch();
		List<Object[]> datas=new ArrayList<Object[]>();
		if(flag.equals("1")){
			String sql1=" select null,null,null,null,null,null,null,null,null,null,'省区发放合计:', sum(m.month_top_award),null,null,sum(m.total),sum(m.month_total) from (SELECT null,t1.group_name,sum(t1.loan_num),sum(t1.convert_loan_num),sum(t1.loan_amount),sum(t1.weight_amount),sum(t1.plat_award),sum(t1.province_plat_award),sum(t1.plat_sale_award),sum(t1.plat_finace_award),sum(t1.pro_plat_finace_award),sum(t1.month_top_award) month_top_award,sum(t1.gro_award),sum(t1.per_award),sum(t1.total) total,sum(t1.month_total) month_total  from   (select t.group_name group_name,t.regional_name regional_name ,sum(t.loan_num) loan_num,sum(t.convert_loan_num) convert_loan_num,sum(t.loan_amount) loan_amount,sum(t.weight_amount) weight_amount,sum(t.plat_award) plat_award,case when t.regional_name ='贵州' then sum(t.loan_amount)*0.00033 else sum(t.loan_amount)*0.00035  end province_plat_award,sum(t.plat_sale_award) plat_sale_award,sum(t.plat_finace_award) plat_finace_award,case when t.regional_name ='福建' then   sum(t.loan_num)*200 else sum(t.loan_amount)/1500  end pro_plat_finace_award,sum(t.month_top_award) month_top_award,sum(t.loan_amount)*0.0002 gro_award,sum(t.convert_loan_num)*40 per_award,sum(t.total)+(CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END) total ,sum(t.month_top_award)+(sum(t.total) + (CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END))*0.6  month_total from  (SELECT hu.group_name, hu.regional_name regional_name,concat(hu.platform_code,'-',hu.platform_name) ltd,count(b.loan_amount) loan_num,sum(b.loan_amount)/300000 convert_loan_num,    sum(b.loan_amount) loan_amount, sum(case when l.loan_num>1 then  b.loan_amount*0.4 else  b.loan_amount*0.6 end )  weight_amount, case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else      sum(b.loan_amount)*0.0007 end  plat_award,null  province_award,    case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end  plat_sale_award,case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end plat_finace_award,	   0 province_finace_award,    0.0 month_top_award,0 hq_award,0 hq_sale_award, ( case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else sum(b.loan_amount)*0.0007 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end)  total,0.0 month_award FROM ods_o2odev_basic_information_after_loan b 	INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id 	WHERE l.loan_type = 'BK'  and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?) 	GROUP BY hu.group_name,hu.regional_name,l.operator_agency ORDER BY hu.group_name,weight_amount ) t group by  regional_name) t1 group by t1.group_name) m ";
			datas=baseService.findObjectsListfromods(sql1,month,month);
		}else if(flag.equals("2")){
			String sql1=" select null,'总计:',sum(m.loan_num),sum(m.convert_loan_num),sum(m.loan_amount),null,null,null,null,null,'总部发放合计:', null,sum(m.gro_award),sum(m.per_award),sum(m.gro_award+m.per_award),sum(m.gro_award+m.per_award)*0.6 from (SELECT null,t1.group_name,sum(t1.loan_num) loan_num,sum(t1.convert_loan_num) convert_loan_num,sum(t1.loan_amount) loan_amount,sum(t1.weight_amount),sum(t1.plat_award),sum(t1.province_plat_award),sum(t1.plat_sale_award),sum(t1.plat_finace_award),sum(t1.pro_plat_finace_award),sum(t1.month_top_award) month_top_award,sum(t1.gro_award) gro_award,sum(t1.per_award) per_award,sum(t1.total) total,sum(t1.month_total) month_total  from   (select t.group_name group_name,t.regional_name regional_name ,sum(t.loan_num) loan_num,sum(t.convert_loan_num) convert_loan_num,sum(t.loan_amount) loan_amount,sum(t.weight_amount) weight_amount,sum(t.plat_award) plat_award,case when t.regional_name ='贵州' then sum(t.loan_amount)*0.00033 else sum(t.loan_amount)*0.00035  end province_plat_award,sum(t.plat_sale_award) plat_sale_award,sum(t.plat_finace_award) plat_finace_award,case when t.regional_name ='福建' then   sum(t.loan_num)*200 else sum(t.loan_amount)/1500  end pro_plat_finace_award,sum(t.month_top_award) month_top_award,sum(t.loan_amount)*0.0002 gro_award,sum(t.convert_loan_num)*40 per_award,sum(t.total)+(CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END) total ,sum(t.month_top_award)+(sum(t.total) + (CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END))*0.6  month_total from  (SELECT hu.group_name, hu.regional_name regional_name,concat(hu.platform_code,'-',hu.platform_name) ltd,count(b.loan_amount) loan_num,sum(b.loan_amount)/300000 convert_loan_num,    sum(b.loan_amount) loan_amount, sum(case when l.loan_num>1 then  b.loan_amount*0.4 else  b.loan_amount*0.6 end )  weight_amount, case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else      sum(b.loan_amount)*0.0007 end  plat_award,null  province_award,    case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end  plat_sale_award,case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end plat_finace_award,	   0 province_finace_award,    0.0 month_top_award,0 hq_award,0 hq_sale_award, ( case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else sum(b.loan_amount)*0.0007 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end)  total,0.0 month_award FROM ods_o2odev_basic_information_after_loan b 	INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id 	WHERE l.loan_type = 'BK'  and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?) 	GROUP BY hu.group_name,hu.regional_name,l.operator_agency ORDER BY hu.group_name,weight_amount ) t group by  regional_name) t1 group by t1.group_name) m ";
			datas=baseService.findObjectsListfromods(sql1,month,month);
		}else{
			String sql1=" select null,null,null,null,null,null,null,null,null,null,'省区发放合计:', sum(month_top_award),null,null,sum(m.gro_award+m.per_award+m.total),sum(month_total)+sum(m.gro_award+m.per_award)*0.6 from (SELECT null,t1.group_name,sum(t1.loan_num),sum(t1.convert_loan_num),sum(t1.loan_amount),sum(t1.weight_amount),sum(t1.plat_award),sum(t1.province_plat_award),sum(t1.plat_sale_award),sum(t1.plat_finace_award),sum(t1.pro_plat_finace_award),sum(t1.month_top_award) month_top_award ,sum(t1.gro_award) gro_award,sum(t1.per_award) per_award,sum(t1.total) total,sum(t1.month_total) month_total  from   (select t.group_name group_name,t.regional_name regional_name ,sum(t.loan_num) loan_num,sum(t.convert_loan_num) convert_loan_num,sum(t.loan_amount) loan_amount,sum(t.weight_amount) weight_amount,sum(t.plat_award) plat_award,case when t.regional_name ='贵州' then sum(t.loan_amount)*0.00033 else sum(t.loan_amount)*0.00035  end province_plat_award,sum(t.plat_sale_award) plat_sale_award,sum(t.plat_finace_award) plat_finace_award,case when t.regional_name ='福建' then   sum(t.loan_num)*200 else sum(t.loan_amount)/1500  end pro_plat_finace_award,sum(t.month_top_award) month_top_award,sum(t.loan_amount)*0.0002 gro_award,sum(t.convert_loan_num)*40 per_award,sum(t.total)+(CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END) total ,sum(t.month_top_award)+(sum(t.total) + (CASE WHEN t.regional_name = '贵州' THEN sum(t.loan_amount) * 0.00033 ELSE sum(t.loan_amount) * 0.00035 END )+(CASE WHEN t.regional_name = '福建' THEN sum(t.loan_num) * 200 ELSE sum(t.loan_amount) / 1500 END))*0.6  month_total from  (SELECT hu.group_name, hu.regional_name regional_name,concat(hu.platform_code,'-',hu.platform_name) ltd,count(b.loan_amount) loan_num,sum(b.loan_amount)/300000 convert_loan_num,    sum(b.loan_amount) loan_amount, sum(case when l.loan_num>1 then  b.loan_amount*0.4 else  b.loan_amount*0.6 end )  weight_amount, case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else      sum(b.loan_amount)*0.0007 end  plat_award,null  province_award,    case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end  plat_sale_award,case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end plat_finace_award,	   0 province_finace_award,    0.0 month_top_award,0 hq_award,0 hq_sale_award, ( case when hu.regional_name='贵州' then  sum(b.loan_amount)*0.0008 else sum(b.loan_amount)*0.0007 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 700  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 900  else   sum(b.loan_amount) / 300000 * 700 end)+(case when hu.regional_name='福建' then  count(b.loan_amount) * 250  when hu.regional_name='贵州'  then  sum(b.loan_amount) / 300000 * 60  else     sum(b.loan_amount) / 300000 * 250 end)  total,0.0 month_award FROM ods_o2odev_basic_information_after_loan b 	INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id 	WHERE l.loan_type = 'BK'  and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?) 	GROUP BY hu.group_name,hu.regional_name,l.operator_agency ORDER BY hu.group_name,weight_amount ) t group by  regional_name) t1 group by t1.group_name) m ";
			datas=baseService.findObjectsListfromods(sql1,month,month);
		}
		logger.info("结束同步O2O金融百亿大战竞赛奖励数据,共耗时[{}]秒",stopWatch.elapsedTime()/1000);
		return datas;
		
	}

	
	public List<Object[]> getMonthRewardData(Date month,String groupName) {
		logger.info("开始同步O2O金融百亿大战竞赛奖励数据");
		StopWatch stopWatch = new StopWatch();
		String sql1="";
		if(groupName.equals("A")){
			sql1="select s.regional_name,s.weight_amount,s.rank, case when (s.loan_amount>10000000 and s.rank=1 and s.regional_name='广西') then 28100.0 when (s.loan_amount>10000000 and s.rank=2 and s.regional_name='广西') then 23100.0 when (s.loan_amount>10000000 and s.rank=3 and s.regional_name='广西') then 18100.0 when (s.loan_amount>10000000 and s.rank=1) then 25000.0 when (s.loan_amount>10000000 and s.rank=2) then 20000.0 when (s.loan_amount>10000000 and s.rank=3) then 15000.0 when s.regional_name='广西'  then 3100.0 else 0.0 end from  (select m.regional_name, m.weight_amount ,(select  count(0)+1  from  (select  hu.group_name, hu.regional_name, sum(case when l.loan_num>1 then  b.loan_amount*0.4 else  b.loan_amount*0.6 end )  weight_amount FROM ods_o2odev_basic_information_after_loan b INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id WHERE l.loan_type = 'BK' and hu.group_name='A' and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?) GROUP BY hu.group_name, hu.regional_name) t where t.weight_amount > m.weight_amount) rank,m.loan_amount from  (select  hu.group_name, hu.regional_name, sum(case when l.loan_num>1 then  b.loan_amount*0.4 else  b.loan_amount*0.6 end )  weight_amount,sum(b.loan_amount)  loan_amount  FROM ods_o2odev_basic_information_after_loan b INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id WHERE l.loan_type = 'BK' and hu.group_name='A' and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?) GROUP BY hu.group_name, hu.regional_name) m order by m.weight_amount) s ";
		}else if(groupName.equals("B")){
			sql1="select s.regional_name,s.weight_amount,s.rank, case when (s.loan_amount>6000000 and s.rank=1) then 10000.0 when (s.loan_amount>6000000 and s.rank=2) then 6000.0 when (s.loan_amount>6000000 and s.rank=3) then 4000.0  else 0.0 end from  (select m.regional_name, m.weight_amount ,(select  count(0)+1  from  (select  hu.group_name, hu.regional_name, sum(case when l.loan_num>1 then  b.loan_amount*0.4 else  b.loan_amount*0.6 end )  weight_amount FROM ods_o2odev_basic_information_after_loan b INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id WHERE l.loan_type = 'BK' and hu.group_name='B' and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?) GROUP BY hu.group_name, hu.regional_name) t where t.weight_amount > m.weight_amount) rank,m.loan_amount from  (select  hu.group_name, hu.regional_name, sum(case when l.loan_num>1 then  b.loan_amount*0.4 else  b.loan_amount*0.6 end )  weight_amount, sum(b.loan_amount)  loan_amount FROM ods_o2odev_basic_information_after_loan b INNER JOIN ods_o2odev_loan_base_info l ON b.bill_code = l.bill_code INNER JOIN ods_o2odev_platform_regional hu ON l.operator_agency = hu.platform_id WHERE l.loan_type = 'BK' and hu.group_name='B' and year(l.loan_time)=year(?)  and month(l.loan_time)=month(?) GROUP BY hu.group_name, hu.regional_name) m order by m.weight_amount) s ";
		}
		//查询奖励数据
		List<Object[]> datas=baseService.findObjectsListfromods(sql1,month,month,month,month);
		logger.info("结束同步O2O金融百亿大战竞赛奖励数据,共耗时[{}]秒",stopWatch.elapsedTime()/1000);
		return datas;
		
	}

	/**
	 * 判断银行额度
	 * @param bank
	 * @return
	 */
	private double decideBankQuota(String bank) {
		double approvalAmount =0;
		if(bank!=null&&bank.equals("中国银行")){
			approvalAmount=1000000000;
		}else if(bank!=null&&bank.equals("国安村镇银行")){
			approvalAmount=200000000;
		}else if(bank!=null&&bank.equals("中国建设银行")){
			approvalAmount=500000000;
		}else if(bank!=null&&bank.equals("江西银行")){
			approvalAmount=1000000000;
		}else if(bank!=null&&bank.equals("郑州银行")){
			approvalAmount=1000000000;
		}else if(bank!=null&&bank.equals("三湘银行")){
			approvalAmount=1000000000;
		}else if(bank!=null&&bank.equals("齐鲁银行")){
			approvalAmount=50000000;
		}
		return approvalAmount;
	}
	
}
